# -*- coding: utf-8 -*-
import json
import pymssql

class Datamanage:
    results = []
    pos_name_trans = {'全部': '%', '花': 'flower', '羽毛': 'feather', '沙漏': 'sand', '杯子': 'cup', '头盔' : 'head'}
    
    #登录
    def login_func(self, input_user, input_password, input_server, input_port):
        try:
            self.conn = pymssql.connect(host = 'localhost', server = input_server, port = input_port, user = input_user, password = input_password, database = 'genshi')
            self.cursor = self.conn.cursor()
            return True
        except:
            return False
    def get_newid(self):
        sql = 'select id from Relics'
        self.cursor.execute(sql)
        results_tul = ''
        results_tul = self.cursor.fetchall()
        results = []
        for i in range(len(results_tul)):
            results.append(results_tul[i][0])
        ID = 10000
        while(True):
            if ID in results:
                ID += 1
            else:
                break
        return ID
    #关闭连接
    def close_conn(self):
        try:
            self.cursor.close()
            self.conn.close()
        except:
            pass
    #插入圣遗物
    def insert_relics(self, attri_dic):
        Tag = []
        #统计用户添加的副词条个数
        for i in range(4):
            if attri_dic['Tag_name' + str(i)]:
                Tag.append(attri_dic['Tag_name' + str(i)])
                Tag.append(float(attri_dic['Tag_value' + str(i)]))
        #根据副词条的个数不同，选择不同的sql语句
        if (len(Tag) == 0):
            sql = "insert into Relics(id, position, setName, detailName, mainTagname, mainTagvalue,grade)values('%d','%s','%s','%s','%s','%f','%d');commit" %(
                attri_dic['id'], attri_dic['pos'], attri_dic['setName'], attri_dic['detailName'], attri_dic['mainTag_name'], float(attri_dic['mainTag_value']), int(attri_dic['level']))
        elif (len(Tag) == 2):
            sql = "insert into Relics(id, position, setName, detailName, mainTagname, mainTagvalue,Tagname_1, value_1, grade)values('%d','%s','%s','%s','%s','%f','%s','%f','%d');commit" %(
                attri_dic['id'], attri_dic['pos'], attri_dic['setName'], attri_dic['detailName'], attri_dic['mainTag_name'], float(attri_dic['mainTag_value']), Tag[0], Tag[1], int(attri_dic['level']))
        elif (len(Tag) == 4):
            sql = "insert into Relics(id, position, setName, detailName, mainTagname, mainTagvalue,Tagname_1, value_1,Tagname_2, value_2, grade)values('%d','%s','%s','%s','%s','%f','%s','%f','%s','%f','%d');commit" %(
                attri_dic['id'], attri_dic['pos'], attri_dic['setName'], attri_dic['detailName'], attri_dic['mainTag_name'], float(attri_dic['mainTag_value']), Tag[0], Tag[1], Tag[2], Tag[3], int(attri_dic['level']))
        elif (len(Tag) == 6):
            sql = "insert into Relics(id, position, setName, detailName, mainTagname, mainTagvalue,Tagname_1, value_1,Tagname_2, value_2,Tagname_3, value_3, grade)values('%d','%s','%s','%s','%s','%f','%s','%f','%s','%f','%s','%f','%d');commit" %(
                attri_dic['id'], attri_dic['pos'], attri_dic['setName'], attri_dic['detailName'], attri_dic['mainTag_name'], float(attri_dic['mainTag_value']), Tag[0], Tag[1], Tag[2], Tag[3], Tag[4], Tag[5],int(attri_dic['level']))
        elif (len(Tag) == 8):
           sql = "insert into Relics(id, position, setName, detailName, mainTagname, mainTagvalue,Tagname_1, value_1,Tagname_2, value_2,Tagname_3, value_3, Tagname_4, value_4,grade)values('%d','%s','%s','%s','%s','%f','%s','%f','%s','%f','%s','%f','%s','%f','%d');commit" %(
                attri_dic['id'], attri_dic['pos'], attri_dic['setName'], attri_dic['detailName'], attri_dic['mainTag_name'], float(attri_dic['mainTag_value']), Tag[0], Tag[1], Tag[2], Tag[3], Tag[4], Tag[5], Tag[6], Tag[7],int(attri_dic['level']))
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    #圣遗物装备         
    def update_equipped(self, ID, character_name):
        sql = "update Relics set equipped = '%s' where id = '%d';commit" %(character_name, int(ID))
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    #删除单条圣遗物记录
    def dele_single_relic(self, ID):
        sql = "delete from Relics where id = '%d';commit" % (int(ID))
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    #筛选查询圣遗物
    def screen_relics(self, select_box, setName, pos, order, type_group, pos_group):
        if setName == '全部':
            setName = '%'
        #将部位信息转换为数据库中的表示形式
        pos = self.pos_name_trans[pos]
        sql = "select * from Relics where setName like '%s' INTERSECT select * from Relics where position like '%s'" % (setName, pos)
        for i in range(len(select_box)):
            if select_box[i] != '%':
                sql += " INTERSECT select * from Relics where mainTagname like '%s' or Tagname_1 like '%s' or Tagname_2 like '%s' or Tagname_3 like '%s' or Tagname_4 like '%s'" % (select_box[i],select_box[i],select_box[i],select_box[i],select_box[i])
        if type_group:
            sql += "ORDER BY setName"
        elif pos_group:
            sql += "ORDER BY position"
        if type_group == False and pos_group == False:
            if order :
                #正序排序
                sql += " ORDER BY grade ASC"
            else:
                #逆序排序
                sql += " ORDER BY grade DESC"
        else:
            if order :
                #正序排序
                sql += ",grade ASC"
            else:
                #逆序排序
                sql += ",grade DESC"
        
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall()
        relics_list = []
        for i in range(len(results)):
            attri_dic = {}
            attri_dic['id'] = results[i][0]
            attri_dic['pos'] = results[i][1]
            attri_dic['setName'] = results[i][2]
            attri_dic['detailName'] = results[i][3]
            attri_dic['mainTag_name'] = results[i][4]
            attri_dic['mainTag_value'] = str(results[i][5])
            for j,k in zip(range(0,8,2),range(4)):
                if results[i][j + 6] != None:
                    attri_dic['Tag_name' + str(k)] = results[i][j + 6]
                    attri_dic['Tag_value' + str(k)] = str(results[i][j + 7])
                else:
                    attri_dic['Tag_name' + str(k)] = False
                    attri_dic['Tag_value' + str(k)] = False
            attri_dic['level'] = results[i][14]
            attri_dic['equipped'] =  results[i][15]
            relics_list.append(attri_dic)
        return relics_list
    
    #插入角色
    def insert_character(self, character_name):
        sql = "insert into Assemble (Name) values('%s');commit" % (character_name)
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    
    #查询属性面板视图
    def select_panel(self, character_name):
        sql = "select * from Character_Panel where Name = '%s'" %(character_name)
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall()
        results = results[0]
        return results
    
    #角色装配武器
    def assemble_weapon(self, character_name, weapon_name):
        sql = "update Assemble set Weapon = '%s' where Name = '%s';commit" %(weapon_name, character_name)
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    #删除角色信息
    def assemble_dele(self, character_name):
        sql = "delete from Assemble where Name = '%s';commit" % (character_name)
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    
    #卸下武器
    def weapon_down(self, character_name):
        sql = "update Assemble set Weapon = NULL where Name = '%s';commit" %(character_name)
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    #卸载圣遗物
    def relics_down(self, character_name, pos):
        sql = "update Assemble set %s = NULL where Name = '%s';commit" %(pos, character_name)     
        try:
            self.cursor.execute(sql)
        except:
            self.conn.rollback()
    
    #查询角色全部装配信息
    def select_assemble(self):
        sql = "select Name, Weapon, Flower, Feather, Sand, Cup, Head from Assemble"
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall()
        return results
    #由id查到圣遗物的名字和位置
    def select_relics_byid(self, ID):
        sql = "select position, detailName from Relics where id = %d" %(int(ID))
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall()        
        return results[0][0], results[0][1]
    #查询武器名字
    def select_weapon(self):
        sql = "select Name from Weapon"
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall() 
        return results
    #根据武器名字查询详细信息
    def select_weapon_detail(self, weapon_name):
        sql = "select * from Weapon where Name = '%s'" %(weapon_name)
        self.cursor.execute(sql)
        results = ''
        results = self.cursor.fetchall() 
        return results[0]
    
data = Datamanage()

    